package cn.com.dao.impl;

import cn.com.bean.TraineeBean;
import cn.com.dao.info.TraineeDaoInfo;
import cn.com.util.DBUtil;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import java.util.List;

public class TraineeDaoImpl implements TraineeDaoInfo {
    @Override
    public List<TraineeBean> findAll() {
        Connection connection = null;
        connection = DBUtil.getConnection();
        List<TraineeBean> list = new ArrayList<>();
        String sql = "select * from trainee where is_del='1'";
        PreparedStatement pst = null;
        ResultSet rs = null;
        TraineeBean traineeBean = null;
        try {
            pst = connection.prepareStatement(sql);
            rs = pst.executeQuery();
            while (rs.next()) {
                traineeBean = new TraineeBean();
                traineeBean.setId(rs.getInt("id"));
                traineeBean.setAge(rs.getInt("age"));
                traineeBean.setName(rs.getString("name"));
                traineeBean.setSex(rs.getString("sex"));
                traineeBean.setSchool(rs.getString("school"));
                traineeBean.setSno(rs.getString("sno"));
                traineeBean.setPhone(rs.getString("email"));
                traineeBean.setStatus(rs.getInt("status"));
                traineeBean.setBeginTime(rs.getString("begin_time"));
                traineeBean.setEndTime(rs.getString("end_time"));
                list.add(traineeBean);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DBUtil.free(connection, pst);
        }
        return list;
    }

    @Override
    public List<TraineeBean> findByName(String where) {
        Connection connection = null;
        connection = DBUtil.getConnection();
        List<TraineeBean> list = new ArrayList<>();
        String sql = "select * from trainee where is_del='1' and name like ?";
        PreparedStatement pst = null;
        ResultSet rs = null;
        TraineeBean traineeBean = null;
        try {
            pst = connection.prepareStatement(sql);
            pst.setString(1, where);
            rs = pst.executeQuery();
            while (rs.next()) {
                traineeBean = new TraineeBean();
                traineeBean.setId(rs.getInt("id"));
                traineeBean.setAge(rs.getInt("age"));
                traineeBean.setName(rs.getString("name"));
                traineeBean.setSex(rs.getString("sex"));
                traineeBean.setSchool(rs.getString("school"));
                traineeBean.setSno(rs.getString("sno"));
                traineeBean.setPhone(rs.getString("email"));
                traineeBean.setStatus(rs.getInt("status"));
                traineeBean.setBeginTime(rs.getString("begin_time"));
                traineeBean.setEndTime(rs.getString("end_time"));
                list.add(traineeBean);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DBUtil.free(connection, pst);
            return null;
        }
    }


    @Override
    public boolean insert(TraineeBean bean) {
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        Connection conn = null;

        try {
            conn = DBUtil.getConnection();
            String sql = "INSERT INTO trainee(name,sex,age,school,sno,phone,email,status,begin_time,end_time,is_del) VALUES(?,?,?,?,?,?,?,?,?,?,?)";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, bean.getName());
            pstmt.setString(2, bean.getSex());
            pstmt.setInt(3, bean.getAge());
            pstmt.setString(4, bean.getSchool());
            pstmt.setString(5, bean.getSno());
            pstmt.setString(6, bean.getPhone());
            pstmt.setString(7, bean.getEmail());
            pstmt.setInt(8, bean.getStatus());
            pstmt.setString(9, bean.getBeginTime());
            pstmt.setString(10, bean.getEndTime());
            pstmt.setInt(11, 1);
            return pstmt.execute();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            DBUtil.free(conn, pstmt, rs);
        }
        return false;
    }

    @Override
    public int update(TraineeBean bean) {
        // TODO Auto-generated method stub
        Connection conn = DBUtil.getConnection();
        PreparedStatement stm = null;
        ResultSet rs = null;
        String sql = "update trainee set  name =? ,sex=? ,age=?,school=?,sno=?,phone=?,email=?,status=?,begin_time=?,end_time=?,is_del where id=?";

        try {
            stm = conn.prepareStatement(sql);
            stm.setString(1, bean.getName() );
            stm.setString(2, bean.getSex());
            stm.setInt(3, bean.getAge());
            stm.setString(4,bean.getSchool()  );
            stm.setString(5, bean.getSno() );
            stm.setString(6, bean.getPhone() );
            stm.setString(7, bean.getEmail() );
            stm.setInt(8, bean.getStatus());
            stm.setString(9, bean.getBeginTime() );
            stm.setString(10, bean.getEndTime() );
            stm.setInt(11, bean.getIsDel() );
            stm.setInt(12, bean.getId() );
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            DBUtil.free(conn, stm,rs );
        }
        return 1;
    }

    @Override
    public int delete(TraineeBean bean) {
        return 0;
    }

    @Override
    public int getIdBySnoSch(String sno, String school) {
        int id = 0;
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        Connection conn = null;
        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT id FROM trainee where sno = ? and school=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, sno);
            pstmt.setString(2, school);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                id = rs.getInt("id");
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();

        } finally {
            DBUtil.free(conn, pstmt, rs);
        }
        return id;
    }

    @Override
    public int getIdByPhone(String phone) {
        int id = 0;
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        Connection conn = null;

        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT id FROM trainee where phone =?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, phone);
            rs = pstmt.executeQuery();
            if (rs.first()) {
                id = rs.getInt("id");
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();

        } finally {
            DBUtil.free(conn, pstmt, rs);
        }
        return id;

    }
}

